#include "formreports.h"
#include "ui_formreports.h"
#ifdef Q_OS_WIN
#include "sleeexcel.h"
#endif
#include <QFileDialog>

FormReports::FormReports(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::FormReports)
{
    ui->setupUi(this);
    QDate qd = QDate::currentDate();
    ui->dedit_sp_rep->setDate(qd);

    ui->combo_year->clear();
    ui->combo_year_2->clear();
    for(int i = 0; i<40; i++)
    {
        ui->combo_year->addItem(QString::number(i+2000));
        ui->combo_year_2->addItem(QString::number(i+2000));
    }
    ui->combo_year->setCurrentIndex(qd.year()-2000);
    ui->combo_year_2->setCurrentIndex(qd.year()-2000);
    ui->combo_month->setCurrentIndex(qd.month()-1);
    ui->combo_month_2->setCurrentIndex(qd.month()-1);

    ui->chb_show_excel->setChecked(false);
    show_excel(false);
    connect(ui->chb_show_excel, SIGNAL(clicked(bool)), this, SLOT(show_excel(bool)));
    connect(ui->dedit_sp_rep, SIGNAL(dateChanged(QDate)), this, SLOT(spaces_date_changed(QDate)));

    connect(ui->combo_month, SIGNAL(currentIndexChanged(int)), this, SLOT(finance_date_changed()));
    connect(ui->combo_year, SIGNAL(currentIndexChanged(int)), this, SLOT(finance_date_changed()));

    connect(ui->combo_month_2, SIGNAL(currentIndexChanged(int)), this, SLOT(private_date_changed()));
    connect(ui->combo_year_2, SIGNAL(currentIndexChanged(int)), this, SLOT(private_date_changed()));
    ui->frame_in_action->hide();
    ui->tabWidget->setCurrentIndex(0);
    connect(ui->btn_export, SIGNAL(clicked()), this, SLOT(export_report()));
}

FormReports::~FormReports()
{
    delete ui;
}

void FormReports::show_excel(bool state)
{
    if(state)
    {
        ui->chb_close_excel->setEnabled(true);
        ui->chb_close_excel->setChecked(true);
    }
    if(!state)
    {
        ui->chb_close_excel->setChecked(true);
        ui->chb_close_excel->setEnabled(false);
    }
}

void FormReports::resizeEvent(QResizeEvent *pe)
{
        resize_spaces_table();
        QWidget::resizeEvent(pe);
}

void FormReports::showEvent(QShowEvent *pe)
{
    gen_rep_spaces(ui->dedit_sp_rep->date());
    finance_date_changed();
    private_date_changed();
    QWidget::showEvent(pe);

}

void FormReports::gen_rep_spaces(QDate qd)
{
    m_rep_s.clear();
    m_rep_s.mqd_target = qd;
    m_rep_s.mqvl_ap_indices = mp_dbms->get_db(LAKDB_APARTMENTS)->findDataRange(-1);
    m_rep_s.mqvl_ap_indices.removeFirst();
    double total_space, by_lease_space, by_nolease_space;
    ui->tw_spaces->clear();
    ui->tw_spaces->setRowCount(0);
    ui->tw_spaces->setColumnCount(4);
    QTableWidgetItem *p_item;
    p_item = new QTableWidgetItem;
    p_item->setText(RUSTR("Адрес:"));
    ui->tw_spaces->setHorizontalHeaderItem(0, p_item);

    p_item = new QTableWidgetItem;
    p_item->setText(RUSTR("Общая площадь (кв.м.):"));
    ui->tw_spaces->setHorizontalHeaderItem(1, p_item);

    p_item = new QTableWidgetItem;
    p_item->setText(RUSTR("По договорам (кв.м.):"));
    ui->tw_spaces->setHorizontalHeaderItem(2, p_item);

    p_item = new QTableWidgetItem;
    p_item->setText(RUSTR("Без договоров (кв.м.):"));
    ui->tw_spaces->setHorizontalHeaderItem(3, p_item);

    int row = 0;
    foreach(QVariant qv_ap_index, m_rep_s.mqvl_ap_indices)
    {
        SLEE_HDB_REC qh_ap_data = mp_dbms->get_data(LAKDB_APARTMENTS, qv_ap_index);
        m_rep_s.mqstrl_addr.append(mp_dbms->get_apartment_addr(qh_ap_data));

        int house_type = mp_dbms->get_house_type(qh_ap_data.value(FLD_APDB_HSINDEX));

        QVariantHash qvh_ap_spaces = qh_ap_data.value(FLD_APDB_LSPSHASH).toHash();
        QVariantHash qvh_ap_pdates = qh_ap_data.value(FLD_APDB_PDATEHASH).toHash();

        QStringList qsl_l_sids;
        QVariantList qvl_l_indices = mp_dbms->get_lease_for_ap(qv_ap_index,m_rep_s.mqd_target);

        foreach(QVariant qv_l_index,qvl_l_indices)
        {
            SLEE_HDB_REC qh_ls_data = mp_dbms->get_data(LAKDB_LEASES,qv_l_index);
            QStringList qsl_l_spaces_ids = qh_ls_data.value(FLD_LS_SPSLST).toStringList();
            qsl_l_sids.append(qsl_l_spaces_ids);
        }
        total_space = 0.0;
        by_lease_space = 0.0;
        by_nolease_space = 0.0;


        foreach(QString qstr_id, qvh_ap_spaces.keys())
        {
            QDate qd_p = qvh_ap_pdates.value(qstr_id).toDate();
            if((qd_p.isValid()) && (qd_p < m_rep_s.mqd_target)) continue;
            double space = qvh_ap_spaces.value(qstr_id).toDouble();
            total_space += space;
            if(qsl_l_sids.contains(qstr_id))
            {
                by_lease_space += space;
            }
            else
                by_nolease_space += space;
        }

        if(house_type == 0)
        {
            double full_space = qh_ap_data.value(FLD_APDB_TSPC).toDouble();
            if(total_space > 0.0) total_space = full_space;
            if(by_lease_space > 0.0) by_lease_space = full_space;
            else
                by_nolease_space = full_space;
        }

        m_rep_s.mql_t_space.append(total_space);
        m_rep_s.mql_l_space.append(by_lease_space);
        m_rep_s.mql_nl_space.append(by_nolease_space);

        ui->tw_spaces->setRowCount(row+1);
        p_item = new QTableWidgetItem(m_rep_s.mqstrl_addr.last());
        ui->tw_spaces->setItem(row,0,p_item);
        p_item = new QTableWidgetItem(QString("%1").arg(total_space));
        ui->tw_spaces->setItem(row,1,p_item);
        p_item = new QTableWidgetItem(QString("%1").arg(by_lease_space));
        ui->tw_spaces->setItem(row,2,p_item);
        p_item = new QTableWidgetItem(QString("%1").arg(by_nolease_space));
        ui->tw_spaces->setItem(row,3,p_item);
        row++;
    }
    total_space = 0.0;
    by_lease_space = 0.0;
    by_nolease_space = 0.0;
    foreach(double a, m_rep_s.mql_t_space) total_space += a;
    foreach(double a, m_rep_s.mql_l_space) by_lease_space += a;
    foreach(double a, m_rep_s.mql_nl_space) by_nolease_space += a;
    ui->ledit_A->setText(QString::number(total_space));
    ui->ledit_B->setText(QString::number(by_lease_space));
    ui->ledit_C->setText(QString::number(by_nolease_space));
    resize_spaces_table();
}

void FormReports::compute_finance_at_month(QDate qd, double *p_finance_4)
{
    p_finance_4[0] = 0.0;
    p_finance_4[1] = 0.0;
    p_finance_4[2] = 0.0;
    p_finance_4[3] = 0.0;

    SLEE_HDB_REC_LST qhl_bills;
    SLEE_HDB_REC_LST qhl_pays;

    mp_dbms->get_db(LAKDB_BILLS)->findDataEq(FLD_BILL_TARGETDATE, QVariant(qd), &qhl_bills);
    mp_dbms->get_db(LAKDB_PAYMENTS)->findDataEq(FLD_PAY_TARGETDATE, QVariant(qd), &qhl_pays);

    foreach(SLEE_HDB_REC qh_bill, qhl_bills)
    {
        double ammount = qh_bill.value(FLD_BILL_AMMOUNT).toDouble();
        int index = 0;
        if(qh_bill.value(FLD_BILL_LINDEX).toULongLong() == 0ll)
        {
            index = 1;
        }
        p_finance_4[index]+=ammount;
    }

    foreach(SLEE_HDB_REC qh_payment, qhl_pays)
    {
        double ammount = qh_payment.value(FLD_PAY_AMMOUNT).toDouble();
        int index = 2;
        if(qh_payment.value(FLD_PAY_LINDEX).toULongLong() == 0ll)
        {
            index = 3;
        }
        p_finance_4[index]+=ammount;
    }
}

void FormReports::gen_rep_finance(QDate qd)
{
    m_rep_f.clear();
    compute_finance_at_month(qd, m_rep_f.m_finance_4);
    ui->ledit_bill_ls_month->setText(QString("%1").arg(m_rep_f.m_finance_4[0], 0,'f',2,QChar(' ')));
    ui->ledit_bill_nls_month->setText(QString("%1").arg(m_rep_f.m_finance_4[1], 0,'f',2,QChar(' ')));
    ui->ledit_pay_ls_month->setText(QString("%1").arg(m_rep_f.m_finance_4[2], 0,'f',2,QChar(' ')));
    ui->ledit_pay_nls_month->setText(QString("%1").arg(m_rep_f.m_finance_4[3], 0,'f',2,QChar(' ')));

    ui->ledit_bill_month->setText(QString("%1").arg(m_rep_f.m_finance_4[0]+m_rep_f.m_finance_4[1], 0,'f',2,QChar(' ')));
    ui->ledit_pay_month->setText(QString("%1").arg(m_rep_f.m_finance_4[2]+m_rep_f.m_finance_4[3], 0,'f',2,QChar(' ')));

    int month = qd.month();
    int year = qd.year();
    double finance_4[4];
    for(int m = 1; m<=month; m++)
    {
        compute_finance_at_month(QDate(year,m,15), finance_4);
        for(int i = 0; i<4; i++)
            m_rep_f.m_yt_finance_4[i]+=finance_4[i];
    }
    ui->ledit_bill_ls_year->setText(QString("%1").arg(m_rep_f.m_yt_finance_4[0], 0,'f',2,QChar(' ')));
    ui->ledit_bill_nls_year->setText(QString("%1").arg(m_rep_f.m_yt_finance_4[1], 0,'f',2,QChar(' ')));
    ui->ledit_pay_ls_year->setText(QString("%1").arg(m_rep_f.m_yt_finance_4[2], 0,'f',2,QChar(' ')));
    ui->ledit_pay_nls_year->setText(QString("%1").arg(m_rep_f.m_yt_finance_4[3], 0,'f',2,QChar(' ')));

    ui->ledit_bill_year->setText(QString("%1").arg(m_rep_f.m_yt_finance_4[0]+m_rep_f.m_yt_finance_4[1], 0,'f',2,QChar(' ')));
    ui->ledit_pay_year->setText(QString("%1").arg(m_rep_f.m_yt_finance_4[2]+m_rep_f.m_yt_finance_4[3], 0,'f',2,QChar(' ')));
    m_rep_f.mqd_target = qd;

}


void FormReports::gen_rep_privates(QDate qd)
{

    QVariantHash qvh_spaces;
    QVariantHash qvh_pdates;
    SLEE_HDB_REC_LST qhl_ap_data;
    mp_dbms->get_db(LAKDB_APARTMENTS)->findDataRange(-1, NULL,true,NULL,false, &qhl_ap_data);
    qhl_ap_data.removeFirst();
    m_rep_p.clear();
    ui->tw_apartments_out->clear();
    ui->tw_apartments_out->setColumnCount(3);
    QTableWidgetItem *p_item;
    p_item = new QTableWidgetItem(RUSTR("Адрес"));
    ui->tw_apartments_out->setHorizontalHeaderItem(0, p_item);
    p_item = new QTableWidgetItem(RUSTR("Площадь"));
    ui->tw_apartments_out->setHorizontalHeaderItem(1, p_item);
    p_item = new QTableWidgetItem(RUSTR("Дата приватизации"));
    ui->tw_apartments_out->setHorizontalHeaderItem(2, p_item);

    int cntr = 0;
    ui->tw_apartments_out->setRowCount(cntr);

    foreach(SLEE_HDB_REC qh_ap, qhl_ap_data)
    {
        qvh_spaces = qh_ap.value(FLD_APDB_LSPSHASH).toHash();
        qvh_pdates = qh_ap.value(FLD_APDB_PDATEHASH).toHash();
        foreach(QString qstr_id, qvh_spaces.keys())
        {
            QDate qd_p = qvh_pdates.value(qstr_id).toDate();
            if(!qd_p.isValid()) continue;
            if(qd_p > qd) continue;
            ui->tw_apartments_out->setRowCount(++cntr);
            m_rep_p.mqstrl_addr.append(mp_dbms->get_apartment_addr(qh_ap));
            m_rep_p.mql_spaces.append(qvh_spaces.value(qstr_id).toDouble());
            m_rep_p.mql_dates.append(qd_p);
            m_rep_p.m_total_space += m_rep_p.mql_spaces.last();
            p_item = new QTableWidgetItem(QString("%1").arg(m_rep_p.mqstrl_addr.last()));
            ui->tw_apartments_out->setItem(cntr-1, 0, p_item);

            p_item = new QTableWidgetItem(QString("%1").arg(m_rep_p.mql_spaces.last()));
            ui->tw_apartments_out->setItem(cntr-1,1, p_item);

            p_item = new QTableWidgetItem(QString("%1").arg(m_rep_p.mql_dates.last().toString("dd.MM.yyyy")));
            ui->tw_apartments_out->setItem(cntr-1, 2, p_item);
        }
    }
    ui->ledit_total_out_space->setText(QString("%1").arg(m_rep_p.m_total_space, 0, 'f',3));
    m_rep_p.mqd_target_date = qd;
    resize_spaces_table();
}


void FormReports::finance_date_changed()
{
    QDate qd = QDate(ui->combo_year->currentIndex() +2000, ui->combo_month->currentIndex() +1, 15);
    gen_rep_finance(qd);
}

void FormReports::private_date_changed()
{
    QDate qd = QDate(ui->combo_year_2->currentIndex()+2000, ui->combo_month_2->currentIndex() +1, 1 );
    gen_rep_privates(qd);
}

void FormReports::spaces_date_changed(QDate qd)
{
    gen_rep_spaces(qd);
}

void FormReports::resize_spaces_table()
{
    int w = ui->tw_spaces->size().width();
    ui->tw_spaces->setColumnWidth(0, w/3);
    ui->tw_spaces->setColumnWidth(1, w/5);
    ui->tw_spaces->setColumnWidth(2, w/5);
    ui->tw_spaces->setColumnWidth(3, w/5);

    w = ui->tw_apartments_out->size().width();
    ui->tw_apartments_out->setColumnWidth(0, w/2);
    ui->tw_apartments_out->setColumnWidth(1, w/4);
    ui->tw_apartments_out->setColumnWidth(2, w/4);
}

void FormReports::export_report()
{
    if(ui->tabWidget->currentIndex() == 0) return report_spaces();
    if(ui->tabWidget->currentIndex() == 1) return report_finance();
    if(ui->tabWidget->currentIndex() == 2) return report_pspaces();
}

void FormReports::report_spaces()
{
#ifdef Q_OS_WIN
   int start_row = 1;
   int total_col = 1;
   int address_col = 1;
   int tot_space_col = 1;
   int ls_spaces_col = 1;
   int nls_spaces_col  = 1;
   int date_row = 1;
   int date_col = 1;
#endif
   QString qstr_template_file = mp_settings->get_doc_dirpath() + "/report_spaces.xls";
   if(!QFile::exists(qstr_template_file))
   {
       lakhMsgE()<<RUSTR("Ошибка. Файл шаблона не найден.");
       return;
   }
   QString qstr_export_dir =  mp_settings->get_export_dirpath();
   if(ui->chb_use_dialog->isChecked())
   {
       QFileDialog qdlg;
       qdlg.setDirectory(qstr_export_dir);
       qdlg.setFileMode(QFileDialog::Directory);
       if(qdlg.exec())
       {
           qstr_export_dir = qdlg.selectedFiles().at(0);
       }
   }
   QString qstr_export_file = qstr_export_dir +"/" +RUSTR("площадь_мж_")+m_rep_s.mqd_target.toString("dd-MM-yyyy")+".xls";

   if(QFile::exists(qstr_export_file)) QFile::remove(qstr_export_file);
   QFile::copy(qstr_template_file, qstr_export_file);

#ifdef Q_OS_WIN

    SleeExcel excel;
    SleeExcelBook *p_book = excel.open_workbook(qstr_export_file);
    SleeExcelSheet *p_sheet = p_book->get_sheet(1);
    if(ui->chb_show_excel->isChecked()) excel.set_visible(true);
    int cells_max = 20 *10;
    int cells_cntr = 0;
    QString qstr_val;
    QVariant qv(QString(" "));
    for(int row = 1; row < 21; row++)
        for(int col = 1; col <11; col++)
        {
            lakhMsgF()<<RUSTR("Сканирование шаблона: %1/ %2").arg(cells_cntr++).arg(cells_max);
            qstr_val = p_sheet->get(row, col).toString();
            qDebug()<<qstr_val;
            if(qstr_val == "$TARGET-DATE") {date_row = row; date_col = col; p_sheet->put(row, col, qv); continue;}
            if(qstr_val == "$TOTAL") {total_col = col; p_sheet->put(row, col,qv); continue;}
            if(qstr_val == "$ADDRESS") {address_col = col; start_row = row; p_sheet->put(row, col, qv); continue;}
            if(qstr_val == "$T-SPC") {tot_space_col = col; p_sheet->put(row, col, qv); continue;}
            if(qstr_val == "$L-SPC")  {ls_spaces_col = col; p_sheet->put(row, col, qv); continue;}
            if(qstr_val == "$NL-SPC") {nls_spaces_col = col; p_sheet->put(row, col, qv); continue;}
        }
qv=m_rep_s.mqd_target.toString("dd.MMMM.yyyy");
p_sheet->put(date_row, date_col, qv);
cells_max = m_rep_s.mqstrl_addr.size();
double total_space = 0.0;
double ls_space = 0.0;
double nls_space = 0.0;
for(int i = 0; i< cells_max; i++)
{
     lakhMsgF()<<RUSTR("Экспорт отчета: %1/ %2").arg(i).arg(cells_max);
     qv = m_rep_s.mqstrl_addr.at(i);
     p_sheet->put(start_row+i, address_col, qv);

     qv = m_rep_s.mql_t_space.at(i);
     total_space+= m_rep_s.mql_t_space.at(i);
     p_sheet->put(start_row+i, tot_space_col, qv);

     qv = m_rep_s.mql_l_space.at(i);
     ls_space += m_rep_s.mql_l_space.at(i);
     p_sheet->put(start_row+i, ls_spaces_col, qv);

     qv = m_rep_s.mql_nl_space.at(i);
     nls_space += m_rep_s.mql_nl_space.at(i);
     p_sheet->put(start_row+i, nls_spaces_col, qv);
}
qv = RUSTR("ВСЕГО:");
p_sheet->put(start_row+cells_max+1, total_col, qv);
qv = total_space;
p_sheet->put(start_row+cells_max+1, tot_space_col, qv);
qv = ls_space;
p_sheet->put(start_row+cells_max+1, ls_spaces_col, qv);
qv = nls_space;
p_sheet->put(start_row+cells_max+1, nls_spaces_col, qv);

lakhMsgF()<<" ";
p_book->save();
if(ui->chb_print->isChecked()) p_sheet->print_out();
if(ui->chb_close_excel->isChecked()) excel.quit_excel();
lakhMsg()<<RUSTR("Отчет экспортирован.")<<qstr_export_file;
#endif

}


void FormReports::report_pspaces()
{
#ifdef Q_OS_WIN
    int start_row = 1;
    int total_col = 1;
    int address_col = 1;
    int pr_space_col = 1;
    int pr_date_col = 1;
    int date_row = 1;
    int date_col = 1;
#endif

    QString qstr_target_date = ui->combo_month_2->itemText(m_rep_p.mqd_target_date.month() -1) + " - ";
    qstr_target_date += QString::number(m_rep_p.mqd_target_date.year());

    QString qstr_template_file = mp_settings->get_doc_dirpath() + "/report_privates.xls";
    if(!QFile::exists(qstr_template_file))
    {
        lakhMsgE()<<RUSTR("Ошибка. Файл шаблона не найден.");
        return;
    }
    QString qstr_export_dir =  mp_settings->get_export_dirpath();
    if(ui->chb_use_dialog->isChecked())
    {
        QFileDialog qdlg;
        qdlg.setDirectory(qstr_export_dir);
        qdlg.setFileMode(QFileDialog::Directory);
        if(qdlg.exec())
        {
            qstr_export_dir = qdlg.selectedFiles().at(0);
        }
    }
    QString qstr_export_file = qstr_export_dir +"/" +RUSTR("выбывшие_площади_")+qstr_target_date+".xls";

    if(QFile::exists(qstr_export_file)) QFile::remove(qstr_export_file);
    QFile::copy(qstr_template_file, qstr_export_file);

 #ifdef Q_OS_WIN

     SleeExcel excel;
     SleeExcelBook *p_book = excel.open_workbook(qstr_export_file);
     SleeExcelSheet *p_sheet = p_book->get_sheet(1);
     if(ui->chb_show_excel->isChecked()) excel.set_visible(true);
     int cells_max = 20 *10;
     int cells_cntr = 0;
     QString qstr_val;
     QVariant qv(QString(" "));
     for(int row = 1; row < 21; row++)
         for(int col = 1; col <11; col++)
         {
             lakhMsgF()<<RUSTR("Сканирование шаблона: %1/ %2").arg(cells_cntr++).arg(cells_max);
             qstr_val = p_sheet->get(row, col).toString();
             qDebug()<<qstr_val;
             if(qstr_val == "$TARGET-M-Y") {date_row = row; date_col = col; p_sheet->put(row, col, qv); continue;}
             if(qstr_val == "$TOTAL") {total_col = col; p_sheet->put(row, col,qv); continue;}
             if(qstr_val == "$ADDRESS") {address_col = col; start_row = row; p_sheet->put(row, col, qv); continue;}
             if(qstr_val == "$P-SPC") {pr_space_col = col; p_sheet->put(row, col, qv); continue;}
             if(qstr_val == "$P-DATE")  {pr_date_col = col; p_sheet->put(row, col, qv); continue;}
         }
 qv=qstr_target_date;
 p_sheet->put(date_row, date_col, qv);
 cells_max = m_rep_p.mqstrl_addr.size();
 for(int i = 0; i< cells_max; i++)
 {
      lakhMsgF()<<RUSTR("Экспорт отчета: %1/ %2").arg(i).arg(cells_max);
      qv = m_rep_p.mqstrl_addr.at(i);
      p_sheet->put(start_row+i, address_col, qv);

      qv = m_rep_p.mql_spaces.at(i);
      p_sheet->put(start_row+i, pr_space_col, qv);

      qv = m_rep_p.mql_dates.at(i).toString("dd.MM.yyyy");
      p_sheet->put(start_row+i, pr_date_col, qv);
 }
 qv = RUSTR("Общая площадь:");
 p_sheet->put(start_row+cells_max+1, total_col, qv);
 qv = m_rep_p.m_total_space;
 p_sheet->put(start_row+cells_max+1, pr_space_col, qv);

 lakhMsgF()<<" ";
 p_book->save();
 if(ui->chb_print->isChecked()) p_sheet->print_out();
 if(ui->chb_close_excel->isChecked()) excel.quit_excel();
 lakhMsg()<<RUSTR("Отчет экспортирован.")<<qstr_export_file;
 #endif
}



void FormReports::report_finance()
{

    QString qstr_target_date = ui->combo_month->itemText(m_rep_f.mqd_target.month() -1) + " - ";
    qstr_target_date += QString::number(m_rep_f.mqd_target.year());

    QString qstr_template_file = mp_settings->get_doc_dirpath() + "/report_finance.xls";
    if(!QFile::exists(qstr_template_file))
    {
        lakhMsgE()<<RUSTR("Ошибка. Файл шаблона не найден.");
        return;
    }
    QString qstr_export_dir =  mp_settings->get_export_dirpath();
    if(ui->chb_use_dialog->isChecked())
    {
        QFileDialog qdlg;
        qdlg.setDirectory(qstr_export_dir);
        qdlg.setFileMode(QFileDialog::Directory);
        if(qdlg.exec())
        {
            qstr_export_dir = qdlg.selectedFiles().at(0);
        }
    }
    QString qstr_export_file = qstr_export_dir +"/" +RUSTR("оплата_за_найм_")+qstr_target_date+".xls";

    if(QFile::exists(qstr_export_file)) QFile::remove(qstr_export_file);
    QFile::copy(qstr_template_file, qstr_export_file);

#ifdef Q_OS_WIN

    QPoint q_bill_month_l;
    QPoint q_bill_month_nl;
    QPoint q_bill_month;
    QPoint q_pay_month_l;
    QPoint q_pay_month_nl;
    QPoint q_pay_month;
    QPoint q_bill_year_l;
    QPoint q_bill_year_nl;
    QPoint q_bill_year;
    QPoint q_pay_year_l;
    QPoint q_pay_year_nl;
    QPoint q_pay_year;
    int date_row = 0, date_col =0;



     SleeExcel excel;
     SleeExcelBook *p_book = excel.open_workbook(qstr_export_file);
     SleeExcelSheet *p_sheet = p_book->get_sheet(1);
     if(ui->chb_show_excel->isChecked()) excel.set_visible(true);
     int cells_max = 20 *10;
     int cells_cntr = 0;
     QString qstr_val;
     QVariant qv(QString(" "));
     for(int row = 1; row < 21; row++)
         for(int col = 1; col <21; col++)
         {
             lakhMsgF()<<RUSTR("Сканирование шаблона: %1/ %2").arg(cells_cntr++).arg(cells_max);
             qstr_val = p_sheet->get(row, col).toString();
             qDebug()<<qstr_val;
             if(qstr_val == "$TARGET-M-Y") {date_row = row; date_col = col; p_sheet->put(row, col, qv); continue;}
             if(qstr_val == "$BILL_MONTH_L") {q_bill_month_l = QPoint(col,row); p_sheet->put(row, col, qv); continue;}
             if(qstr_val == "$BILL_MONTH_NL") {q_bill_month_nl = QPoint(col,row); p_sheet->put(row, col, qv); continue;}
             if(qstr_val == "$BILL_MONTH") {q_bill_month = QPoint(col,row); p_sheet->put(row, col, qv); continue;}
             if(qstr_val == "$PAY_MONTH_L") {q_pay_month_l = QPoint(col,row); p_sheet->put(row, col, qv); continue;}
             if(qstr_val == "$PAY_MONTH_NL") {q_pay_month_nl = QPoint(col,row); p_sheet->put(row, col, qv); continue;}
             if(qstr_val == "$PAY_MONTH") {q_pay_month = QPoint(col,row); p_sheet->put(row, col, qv); continue;}

             if(qstr_val == "$BILL_YEAR_L") {q_bill_year_l = QPoint(col,row); p_sheet->put(row, col, qv); continue;}
             if(qstr_val == "$BILL_YEAR_NL") {q_bill_year_nl = QPoint(col,row); p_sheet->put(row, col, qv); continue;}
             if(qstr_val == "$BILL_YEAR") {q_bill_year = QPoint(col,row); p_sheet->put(row, col, qv); continue;}
             if(qstr_val == "$PAY_YEAR_L") {q_pay_year_l = QPoint(col,row); p_sheet->put(row, col, qv); continue;}
             if(qstr_val == "$PAY_YEAR_NL") {q_pay_year_nl = QPoint(col,row); p_sheet->put(row, col, qv); continue;}
             if(qstr_val == "$PAY_YEAR") {q_pay_year = QPoint(col,row); p_sheet->put(row, col, qv); continue;}
         }

     qv=qstr_target_date;
     p_sheet->put(date_row, date_col, qv);

     qv = m_rep_f.m_finance_4[0]; p_sheet->put(q_bill_month_l.y(), q_bill_month_l.x(), qv);
     qv = m_rep_f.m_finance_4[1]; p_sheet->put(q_bill_month_nl.y(), q_bill_month_nl.x(), qv);
     qv = m_rep_f.m_finance_4[2]; p_sheet->put(q_pay_month_l.y(), q_pay_month_l.x(), qv);
     qv = m_rep_f.m_finance_4[3]; p_sheet->put(q_pay_month_nl.y(), q_pay_month_nl.x(), qv);

     qv = m_rep_f.m_finance_4[0]+m_rep_f.m_finance_4[1];
     p_sheet->put(q_bill_month.y(), q_bill_month.x(), qv);
     qv = m_rep_f.m_finance_4[2]+m_rep_f.m_finance_4[3];
     p_sheet->put(q_pay_month.y(), q_pay_month.x(), qv);

     qv = m_rep_f.m_yt_finance_4[0]; p_sheet->put(q_bill_year_l.y(), q_bill_year_l.x(), qv);
     qv = m_rep_f.m_yt_finance_4[1]; p_sheet->put(q_bill_year_nl.y(), q_bill_year_nl.x(), qv);
     qv = m_rep_f.m_yt_finance_4[2]; p_sheet->put(q_pay_year_l.y(), q_pay_year_l.x(), qv);
     qv = m_rep_f.m_yt_finance_4[3]; p_sheet->put(q_pay_year_nl.y(), q_pay_year_nl.x(), qv);

     qv = m_rep_f.m_yt_finance_4[0]+m_rep_f.m_yt_finance_4[1];
     p_sheet->put(q_bill_year.y(), q_bill_year.x(), qv);
     qv = m_rep_f.m_yt_finance_4[2]+m_rep_f.m_yt_finance_4[3];
     p_sheet->put(q_pay_year.y(), q_pay_year.x(), qv);


    p_book->save();
    if(ui->chb_print->isChecked()) p_sheet->print_out();
    if(ui->chb_close_excel->isChecked()) excel.quit_excel();
    lakhMsg()<<RUSTR("Отчет экспортирован.")<<qstr_export_file;
#endif
}
